﻿<%@ WebService Language="C#" Class="aggiorna" %>

using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;


// definisce la struttura della lista progetti da ritornare con GetProjectsList
public class Projects
{
    public int ProjectId { get; set; }
    public string ProjectName { get; set; }
}

public class Spese
{
    public int SpeseId { get; set; }
    public string SpeseName { get; set; }
}

public class TipoOre
{
    public int TipoOreId { get; set; }
    public string TipoOreName { get; set; }
}

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]

public class aggiorna  : System.Web.Services.WebService {

    // Leggi tipo Ore
    [WebMethod]
    public List<TipoOre> GetTipoOreList()
    {

        var ReturnList = new List<TipoOre>();

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSql12155ConnectionString"].ConnectionString);
        SqlDataAdapter adapter = new SqlDataAdapter("SELECT HourType_Id, HourTypeCode + ' : ' + Name AS codice FROM HourType ORDER BY HourTypeCode", conn);
        SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

        DataSet ds = new DataSet("DSTipoOreList");
        adapter.Fill(ds, "TipoOreList");


        int i = 0;
        foreach (DataRow rs in ds.Tables[0].Rows)
        {

            var emp = new TipoOre
            {
                TipoOreId = (int)rs[0],
                TipoOreName = rs[1].ToString()
            };
            ReturnList.Add(emp);
        }   
        
        // Return JSON data         
        return ReturnList;

    } // GetTipoOreList
    
    //  ***** Leggi Spese ****   
    [WebMethod]
    public List<Spese> GetSpeseList(int Person_id)
    {

        var ReturnList = new List<Spese>();

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSql12155ConnectionString"].ConnectionString);
        SqlDataAdapter adapter = new SqlDataAdapter("SELECT ForcedExpensesPers.ExpenseType_Id, ExpenseType.ExpenseCode + ' : ' + ExpenseType.Name AS codice FROM ForcedExpensesPers RIGHT JOIN ExpenseType ON ForcedExpensesPers.ExpenseType_Id = ExpenseType.ExpenseType_Id WHERE ( ( ForcedExpensesPers.Persons_id=" + Person_id.ToString() + " ) AND ( ExpenseType.Active=1 ) )  ORDER BY ExpenseType.ExpenseCode", conn);
        SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

        DataSet ds = new DataSet("DSSpeseList");
        adapter.Fill(ds, "SpeseList");


        int i = 0;
        foreach (DataRow rs in ds.Tables[0].Rows)
        {

            var emp = new Spese
            {
                SpeseId = (int)rs[0],
                SpeseName = rs[1].ToString()
            };
            ReturnList.Add(emp);
        }

        // Return JSON data         
        return ReturnList;

    } // GetSpesaList
    
     //  ***** Leggi progetti ****   
    [WebMethod]
    public List<Projects> GetProjectsList(int Person_id)
    {
        
        var ReturnList = new List<Projects>();
        
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSql12155ConnectionString"].ConnectionString);
        SqlDataAdapter adapter = new SqlDataAdapter("SELECT ForcedAccounts.Projects_Id, ProjectCode, ([ProjectCode] + ' ' + [Name]) as PrgNames FROM ForcedAccounts RIGHT JOIN Projects ON ForcedAccounts.Projects_id = Projects.Projects_Id WHERE ( ( ForcedAccounts.Persons_id=" + Person_id.ToString() + " ) AND ( projects.Active=1 ) )  ORDER BY Projects.ProjectCode", conn);
        SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
        
        DataSet ds = new DataSet("DSProjectsList");
        adapter.Fill(ds, "ProjectsList");
        
                
        int i = 0;
        foreach (DataRow rs in ds.Tables[0].Rows)
           {
            
            var emp = new Projects
                            {
                            ProjectId = (int) rs[0],
                            ProjectName = rs[2].ToString()
                            };
            ReturnList.Add(emp);               
        }
    
        // Return JSON data         
        return ReturnList;
        
    } // GetProjectsList
    
    
//  ***** Aggiorna Ore ****   
    [WebMethod]
    public string salvaore(string TbdateForHours, int TbHours, int Person_id, int Projects_Id, int HourType_Id, string comment, bool TransferFlag, bool CancelFlag)
         {
             string sResult;
        
             SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSql12155ConnectionString"].ConnectionString);
             
             try
               {
               conn.Open();                   
               SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Hours", conn);
               SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

               // Create a dataset object
               DataSet ds = new DataSet("DSHours");
               adapter.Fill(ds, "Hours");

               // Create a data table object and add a new row
               DataTable HoursTable = ds.Tables["Hours"];
               DataRow row = HoursTable.NewRow();
              
               row["Date"] = TbdateForHours;
               row["Projects_Id"] = Projects_Id;
               row["Persons_Id"] = Person_id;
               row["HourType_Id"] = HourType_Id;
               row["Hours"] = (CancelFlag == true ? -1 : 1) * TbHours;
               row["comment"] = comment.ToString();
               row["TransferFlag"] = TransferFlag; 
               row["CancelFlag"] = CancelFlag;
               HoursTable.Rows.Add(row);

               // Update data adapter
               adapter.Update(ds, "Hours");
               }
             catch (Exception exp)
             {
             //    Console.WriteLine("Error: " + exp);
                 sResult = exp.ToString();
             }
             finally
             {
                 conn.Close();
                 sResult = "true";
             }
             return (sResult);
    }

    //  ***** Aggiorna Spese ****   
    [WebMethod]
    public string salvaspese(string Tbdate, string TbExpenseAmount, int Person_id, int Projects_Id, int ExpenseType_Id, string comment, bool CreditCardPayed, bool CancelFlag, bool InvoiceFlag)
    {
        string sResult;

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSql12155ConnectionString"].ConnectionString);

        try
        {
            conn.Open();
            SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Expenses", conn);
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

            // Create a dataset object
            DataSet ds = new DataSet("DSExpenses");
            adapter.Fill(ds, "Expenses");

            // Create a data table object and add a new row
            DataTable ExpensesTable = ds.Tables["Expenses"];
            DataRow row = ExpensesTable.NewRow();

            TbExpenseAmount = TbExpenseAmount.Replace('.', ',');
            
            row["Date"] = Tbdate;
            row["Projects_Id"] = Projects_Id;
            row["Persons_Id"] = Person_id;
            row["ExpenseType_Id"] = ExpenseType_Id;
            row["amount"] = (CancelFlag == true ? -1 : 1) * Convert.ToSingle(TbExpenseAmount);
            row["comment"] = comment.ToString();
            row["CreditCardPayed"] = CreditCardPayed;
            row["CancelFlag"] = CancelFlag;
            row["InvoiceFlag"] = InvoiceFlag;

            ExpensesTable.Rows.Add(row); ;

            // Update data adapter
            adapter.Update(ds, "Expenses");
        }
        catch (Exception exp)
        {
            //    Console.WriteLine("Error: " + exp);
            sResult = exp.ToString();
        }
        finally
        {
            conn.Close();
            sResult = "true";
        }
        return (sResult);
    }
}